using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Security.Cryptography;
using System.Collections;
using System.Windows.Forms;

namespace DailyTransaction
{
    class BAO
    {
        private DAO dao = new DAO();
        
        public float round_dc(float pValue ,int pDecimal_place)
        {
            float uTemp_Value;
            int uDecimal;

            uDecimal = pDecimal_place;
            uTemp_Value = pValue * (10 ^ uDecimal);

            if ((uTemp_Value - (int)(uTemp_Value)) >= 0.5)
                uTemp_Value = (int)(uTemp_Value + 1);
            else
                uTemp_Value = (int)(uTemp_Value);

            float t = uTemp_Value / (10 ^ uDecimal);
            return t;
        }

        public DataTable get_qCheck_curr_revenue()
        {
            string sql = @" SELECT revenue.DATE_VISIT, Count(revenue.INV_NBER) AS CountOfINV_NBER
                            FROM revenue
                            GROUP BY revenue.DATE_VISIT
                            ORDER BY revenue.DATE_VISIT";
            DataTable tbl = dao.executeQuery(sql);
            return tbl;
        }
        public DataTable get_qCMS_Center()
        {
            string sql = @"SELECT distinct tVAT_Center.CMS_Center, tVAT_Center.CMS_Center_Desc
                           FROM tVAT_Center
                           WHERE tVAT_Center.Select_ind = 1
                           GROUP BY tVAT_Center.CMS_Center, tVAT_Center.CMS_Center_Desc";
            DataTable tbl = dao.executeQuery(sql);
            return tbl;
        }

        public void Delete_temp_Revenue()
        {
            dao.Delete_temp_revenue();
        }

        internal void Delete_temp_Proc_Phar()
        {
            dao.Delete_temp_Proc_Phar();
        }

        internal void Duplicate_curr_Revenue_Direct(string fromDate, string toDate, string cmscenter)
        {
            dao.Duplicate_curr_Revenue_Direct(fromDate, toDate, cmscenter);
        }

        internal int Duplicate_Revenuey(string fromDate, string toDate, string cmscenter)
        {
            return dao.Duplicate_Revenuey(fromDate, toDate, cmscenter);
        }

        internal int Duplicate_Revenuey_Direct(string fromDate, string toDate, string cmscenter)
        {
            return dao.Duplicate_Revenuey_Direct(fromDate, toDate, cmscenter);
        }

        internal int Duplicate_Revenuev(string fromDate, string toDate, string cmscenter)
        {
            return dao.Duplicate_Revenuev(fromDate, toDate, cmscenter);
        }

        internal void Duplicate_curr_Revenue(string fromDate, string toDate, string cmscenter)
        {
            dao.Duplicate_curr_Revenue(fromDate, toDate, cmscenter);
        }

        internal void Duplicate_Curr_Act_proc(string fromDate, string toDate, string cmscenter)
        {
            dao.Duplicate_Curr_Act_proc(fromDate, toDate, cmscenter);
        }

        internal void Duplicate_Act_proy(string fromDate, string toDate, string cmscenter)
        {
            dao.Duplicate_Act_proy(fromDate, toDate, cmscenter);
        }

        internal void Duplicate_Act_prov(string fromDate, string toDate, string cmscenter)
        {
            dao.Duplicate_Act_prov(fromDate, toDate, cmscenter);
        }

        internal void Duplicate_curr_Act_phar(string fromDate, string toDate, string cmscenter)
        {
            dao.Duplicate_curr_Act_phar(fromDate, toDate, cmscenter);
        }

        internal void Duplicate_Act_phay(string fromDate, string toDate, string cmscenter)
        {
            dao.Duplicate_Act_phay(fromDate, toDate, cmscenter);
        }

        internal void Duplicate_Act_phav(string fromDate, string toDate, string cmscenter)
        {
            dao.Duplicate_Act_phav(fromDate, toDate, cmscenter);
        }

        //internal void Batch_update_Invoice_type()
        //{
        //    dao.Batch_update_Invoice_type();
        //}
        internal void Batch_Update_Doc_code()
        {
            dao.Batch_Update_Doc_code();
        }

        internal void Append_Rev2Table()
        {
            dao.Append_Rev2Table();
        }

        internal void Append_Proc_Phar2Table()
        {
            dao.Append_Proc_Phar2Table();
        }

        internal void qAppend_VAT_CMS_NUM(string fromdate, string todate)
        {
            dao.qAppend_VAT_CMS_NUM(fromdate, todate);
        }

        internal void qUpdate_PayAccountCode_AC_GTE(string fromdate, string todate)
        {
            dao.qUpdate_PayAccountCode_AC_GTE(fromdate, todate);
        }

        internal void qUpdate_PayAccountCode_OUTSTD(string fromdate, string todate)
        {
            dao.qUpdate_PayAccountCode_OUTSTD(fromdate, todate);
        }

        internal void qUpdate_CCP(string fromdate, string todate)
        {
            dao.qUpdate_CCP(fromdate, todate);
        }

        internal void qDelete_AV_INV_To_Temp_VAT_Void()
        {
            dao.qDelete_AV_INV_To_Temp_VAT_Void();
        }

        internal void qAppend_AV_INV_To_Temp_VAT_Void(string fromdate, string todate)
        {
            dao.qAppend_AV_INV_To_Temp_VAT_Void(fromdate, todate);
        }

        internal void Update_ACC_Void_Inv(string fromdate, string todate)
        {
            dao.Update_ACC_Void_Inv(fromdate, todate);
        }

        internal DataTable get_Temp_Proc_phar()
        {
            string sql = @"SELECT temp_Proc_Phar.CMS_Center, temp_Proc_Phar.INV_NBER, temp_Proc_Phar.DATE_VISIT, temp_Proc_Phar.UPD_DOC_CODE, temp_Proc_Phar.PAT_FILCOD, temp_Proc_Phar.PROC_PHAR_NBER, temp_Proc_Phar.PROC_PHAR_IND, temp_Proc_Phar.PROC_PHAR_CODE, temp_Proc_Phar.PROC_PHAR_NAME, temp_Proc_Phar.PROC_PHAR_TYPE, temp_Proc_Phar.PROC_PHAR_X, temp_Proc_Phar.PROC_PHAR_PAY, temp_Proc_Phar.INV_STAT, temp_Proc_Phar.UPD_DOC_ALIAS, temp_Proc_Phar.UPD_INV_STAT, temp_Proc_Phar.UPD_SUN_T2, temp_Proc_Phar.UPD_STAT_Cat
                            FROM temp_Proc_Phar
                            ORDER BY temp_Proc_Phar.CMS_Center, temp_Proc_Phar.INV_NBER, temp_Proc_Phar.DATE_VISIT, temp_Proc_Phar.UPD_DOC_CODE DESC";
            DataTable tbl = dao.executeQuery(sql);
            return tbl;
        }

        public DataTable get_Temp_Revenue()
        {
            string sql = @"select CMS_Center,INV_NBER,UPD_Stat_Cat from qTemp_Revenue";
            DataTable tbl = dao.executeQuery(sql);
            return tbl;
        }

        public bool update_temp_revenue(string uStat_Cat, string uCMS_Center, string uOld_Inv_Nber)
        {
           string SQLStr = "Update temp_Revenue  SET UPD_STAT_CAT = '" + uStat_Cat.Trim() + "'"
                                + "  where CMS_Center = '" + uCMS_Center.Trim() + "'"
                                + "  and INV_Nber = '" + uOld_Inv_Nber.Trim() + "'";
           try
           {
               dao.executeNonquery(SQLStr);               
           }
           catch
           {              
               return false;
           }
           return true;
        }

        internal bool update_Temp_Proc_Phar(string UPD_Stat_Cat,string CMS_Center,string INV_NBER)
        {
            string SQLStr = "Update temp_proc_phar set UPD_STAT_CAT  = '" + UPD_Stat_Cat.Trim() + "'"
                            + " where CMS_Center = '" + CMS_Center.Trim() + "'"
                            + " and INV_Nber = '" + INV_NBER.Trim() + "';";
            try
            {
                dao.executeNonquery(SQLStr);
            }
            catch
            {
                return false;
            }
            return true;
        }
        public void Batch_update_Doc_code()
        {
            string uInv_nber, uOld_Inv_Nber, uDoc_Code, uDoc_Alias;
            int tCount = 0;
            string sql;

            DataTable tbl = get_Temp_Proc_phar();
            if (tbl.Rows.Count > 0)
            {
                uOld_Inv_Nber = tbl.Rows[0]["INV_NBER"].ToString();
                uDoc_Code = tbl.Rows[0]["UPD_DOC_CODE"] == null ? " " : tbl.Rows[0]["UPD_DOC_CODE"].ToString();
                uDoc_Alias = tbl.Rows[0]["UPD_DOC_ALIAS"] == null ? " " : tbl.Rows[0]["UPD_DOC_ALIAS"].ToString();

                for (int i = 0; i < tbl.Rows.Count;i++ )
                {
                    DataRow r = tbl.Rows[i];
                    tCount = tCount + 1;
                    uInv_nber = r["INV_NBER"].ToString();

                    // when change of Invoice Number then get the new Doctor Code
                    if (uInv_nber != uOld_Inv_Nber)
                    {
                        uDoc_Code = r["UPD_DOC_CODE"] == null ? " " : r["UPD_DOC_CODE"].ToString();
                        uDoc_Alias = r["UPD_DOC_ALIAS"] == null? " ": r["UPD_DOC_ALIAS"].ToString();
                        uOld_Inv_Nber = uInv_nber;
                    }

                    if (r["UPD_DOC_CODE"] == null || r["UPD_DOC_CODE"].ToString().Trim() == "") 
                    {
                        string doc_code, doc_alias;
                        if (r["UPD_Stat_Cat"].ToString() == "E")
                        {
                            //r["UPD_DOC_CODE"] = "002";
                            //r["UPD_DOC_ALIAS"] = "Emergency RM";
                            doc_code = "002";
                            doc_alias = "Emergency RM";                           
                        }
                        else
                        {
                            //r["UPD_DOC_CODE"] = uDoc_Code;
                            //r["UPD_DOC_ALIAS"] = uDoc_Alias;
                            doc_code = uDoc_Code;
                            doc_alias = uDoc_Alias;   

                        }
                        sql = "UPDATE temp_Proc_Phar "
                                  + " SET UPD_DOC_CODE='" + doc_code + "' ," 
                                  + " UPD_DOC_ALIAS = '" + doc_alias + "' "
                                  + " WHERE CMS_Center='" + r["CMS_Center"].ToString() + "' "
                                  + " AND DATE_VISIT='" + r["DATE_VISIT"].ToString() + "' "
                                  + " AND INV_NBER='" + r["INV_NBER"].ToString() + "' "
                                  + " AND PROC_PHAR_CODE='" + r["PROC_PHAR_CODE"].ToString() + "' "
                                  + " AND PROC_PHAR_NBER='" + r["PROC_PHAR_NBER"].ToString() + "'";
                        dao.executeNonquery(sql);
                    }
                }
            }

        }
        public void Batch_update_Invoice_type()
        {
            string uInv_nber, uOld_Inv_Nber, uMain_AreaCode, uStat_Cat;
            string uCMS_Center;

            // 1st Pass - to get the Statistic Category (from Main Area code) and update to the Revenue table
            DataTable tbl1 = get_Temp_Proc_phar();
            if (tbl1.Rows.Count > 0)
            {
                uOld_Inv_Nber = tbl1.Rows[0]["INV_NBER"].ToString();
                uCMS_Center = tbl1.Rows[0]["CMS_Center"].ToString();
                uStat_Cat = "P";
                for (int i = 0; i < tbl1.Rows.Count; i++)
                {
                    DataRow r = tbl1.Rows[i];
                    uInv_nber = r["INV_NBER"].ToString();
                    // When change of Invoice Number then get the new Doctor Code
                    if (uInv_nber != uOld_Inv_Nber)
                    {
                        update_temp_revenue(uStat_Cat, uCMS_Center, uOld_Inv_Nber);
                        uOld_Inv_Nber = uInv_nber;
                        uStat_Cat = "P";
                    }

                    // If procedure line then check for it Statistic Category..
                    if (r["PROC_PHAR_IND"].ToString() == "P")          // it is a Procedure line (not a Drug/Dispos)
                    {
                        uMain_AreaCode = r["PROC_PHAR_CODE"].ToString().Substring(0, 1);
                        if (uStat_Cat == "E")
                            //Ignore -  if already grouped as Emtergenc/InPatient then ignore
                            ;
                        else
                            if (uMain_AreaCode == "E")
                                uStat_Cat = "E";
                            else
                                if (uStat_Cat == "D")
                                    // ignore
                                    ;
                                else
                                    if (uMain_AreaCode == "D")
                                        uStat_Cat = "D";
                                    else
                                        uStat_Cat = "C";
                    }
                }
                // Update for the last record - change of
                string SQLStr = "Update temp_Revenue  SET UPD_STAT_CAT = '" + uStat_Cat.Trim() + "'"
                                + "  where CMS_Center = '" + uCMS_Center.Trim() + "'"
                                + "  and INV_Nber = '" + uOld_Inv_Nber.Trim() + "'";
                dao.executeNonquery(SQLStr);
            }

            // 2nd Pass - Get the Stat_cat from the Revenue table and update it back to the temp_Phar_Proc table
            DataTable tbl2 = get_Temp_Revenue();
            if (tbl2.Rows.Count > 0)
            {
                foreach (DataRow rr in tbl2.Rows)
                {
                    update_Temp_Proc_Phar(rr["UPD_Stat_Cat"].ToString(),
                                                rr["CMS_Center"].ToString(),
                                                rr["INV_NBER"].ToString());
                }
            }
        }

        internal void Update_totals_temp_revenue(string CMSCenter)
        {
            dao.Update_totals_temp_revenue(CMSCenter);
        }

        internal void Update_Rounding_Totals()
        {
           string SQLStr;
           string uCMS_Center, uInv_nber;
           float TOTAL_PROC, TOTAL_PHAR, TOTAL_PAY, VAT_AMOUNT;
            SQLStr = @" SELECT * FROM temp_Revenue WHERE date_visit>='04-Jun-2009'";

            DataTable tbl1 = dao.executeQuery(SQLStr);            
            if (tbl1.Rows.Count<0)
                return;

            foreach(DataRow r in tbl1.Rows)
            {
                uInv_nber = r["INV_NBER"].ToString();
                uCMS_Center = r["CMS_Center"].ToString();
               ;
               TOTAL_PROC = round_dc(float.Parse(r["TOTAL_PROC"].ToString()), 2);
               TOTAL_PHAR = round_dc(float.Parse(r["TOTAL_PHAR"].ToString()), 2);
               TOTAL_PAY = round_dc(float.Parse(r["TOTAL_PAY"].ToString()), 2);
               VAT_AMOUNT = round_dc(float.Parse(r["VAT_AMOUNT"].ToString()), 2);
                dao.Update_Rounding_Totals(uCMS_Center, uInv_nber, TOTAL_PROC, TOTAL_PHAR, TOTAL_PAY, VAT_AMOUNT);                    
            }
        }

        internal void Update_pays_temp_proc_phar(string CMSCenter)
        {
            dao.Update_pays_temp_proc_phar(CMSCenter);
        }

        internal void Update_Rounding_Pays_Temp_proc_phar()
        {
            string SQLStr;
            string uCMS_Center, uInv_nber, uProc_phar_code, uProc_phar_nber;
            float PROC_PHAR_PAY;
            
            SQLStr = @" SELECT * FROM temp_Proc_phar WHERE date_visit>='04-Jun-2009'";

            DataTable tbl1 = dao.executeQuery(SQLStr);
            if (tbl1.Rows.Count < 0)
                return;

            foreach (DataRow r in tbl1.Rows)
            {
                uInv_nber = r["INV_NBER"].ToString();
                uCMS_Center = r["CMS_Center"].ToString();
                uProc_phar_code = r["PROC_PHAR_CODE"].ToString();
                uProc_phar_nber = r["PROC_PHAR_NBER"].ToString();                              
                PROC_PHAR_PAY = round_dc(float.Parse(r["PROC_PHAR_PAY"].ToString()), 2);
               
                dao.Update_Rounding_Temp_proc_phar(uCMS_Center, uInv_nber, uProc_phar_code,uProc_phar_nber, PROC_PHAR_PAY);
            }
        }

        internal DataTable getInfoUser(string p)
        {
            throw new Exception("The method or operation is not implemented.");
        }
        // method to encode password
        public string EncodePassword(string userpassword)
        {
            Byte[] originalPwdBytes;
            Byte[] encodedPwdBytes;

            MD5 md5;
            //Instantiate MD5CryptoServiceProvider, get bytes for user's original password and encode password in MD5 format.
            md5 = new MD5CryptoServiceProvider();
            originalPwdBytes = ASCIIEncoding.Default.GetBytes(userpassword);
            //Convert encoded user password in 'readable" format.
            encodedPwdBytes = md5.ComputeHash(originalPwdBytes);
            //Convert encoded user password in 'readable" format.  
            string encodedStr = BitConverter.ToString(encodedPwdBytes);
            
            return encodedStr;
        }


        internal void Insert_User_account(string fname, string lname, string userID, string encodedPass, int role)
        {
            string sql = "Insert into tblUser values('', '" + fname + "', '"
                        + lname + "', '" + userID + "', '" + encodedPass + "', '"
                        + role + "', 1 , GETDATE())";
            dao.executeNonquery(sql);                
        }

        internal bool Check_Username_Pass(string userID, string encodedPass)
        {
            bool isvalid = false;
            string sql = "SELECT * from tblUser WHERE userID='" + userID + "'"
                        + " AND userPassword='" + encodedPass + "'"
                        + " AND valid_flag = 1";
            DataTable tbl = dao.executeQuery(sql);
            if (tbl.Rows.Count > 0)
                isvalid = true;
            return isvalid;
        }
        //==============================daily_checking====
        public Double round_dc(Double pValue, int pDecimal_place)
        {
            Double uTemp_Value;
            int uDecimal;

            uDecimal = pDecimal_place;
            uTemp_Value = pValue * (10 ^ uDecimal);

            if ((uTemp_Value - (Int32)(uTemp_Value)) >= 0.5)
                uTemp_Value = (Int32)(uTemp_Value + 1);
            else
                uTemp_Value = (Int32)(uTemp_Value);

            double t = uTemp_Value / (10 ^ uDecimal);
            return t;
        }
       
        public DataTable get_qVAT_Center()
        {
            string sql = @"SELECT distinct tVAT_Center.[VAT Center]
                           FROM tVAT_Center
                           WHERE tVAT_Center.Select_ind = 1
                           ORDER BY tVAT_Center.[VAT Center]";
            DataTable tbl = dao.executeQuery(sql);

            return tbl;
        }

        public DataTable get_Paymode()
        {
            /*string sql = @"SELECT distinct pay_mode from revenuey
                           WHERE date_visit is not null
                            AND inv_nber is not null
                            AND pay_mode is not null
                            ORDER BY pay_mode";*/
            string sql = "SELECT * FROM pmnt_mode WHERE valid_flag='1'"
                            + " Order by pay_mode";
            DataTable tbl = dao.executeQuery(sql);

            return tbl;
        }
        public DataTable get_Paymode_revised()
        {
            string sql = "SELECT DISTINCT kind_pment FROM ab_account_code "
                         + " UNION Select '' ";
            DataTable tbl = dao.executeQuery(sql);

            return tbl;
        }

        public DataTable get_Pment_Desc()
        {
            string sql = "SELECT DISTINCT KIND_PMENT_DESC FROM AB_ACCOUNT_CODE"
                        + " UNION Select ''";
            return dao.executeQuery(sql);
        }

        public DataTable get_Invoices_unSubmitted(string date_type, string pmt_mode, string pVATCenter, string fromdate, string todate)
        { //pmnt_mode_revised,
            //modified INNER JOIN become to "LEFT JOIN" (1/7/2009)
            //added upd_remark column on 9/7/2009
            string sql = "select a.Date_visit,a.CMS_number,a.VAT_number,a.VAT_number_revised,a.upd_pay_acctcode, a.pmnt_mode_revised"
                        + ",a.upd_payment_date,a.patient_name,a.total_proc,a.total_phar,a.total_amt,a.total_amt_vnd,a.rate_curr, "
                        + "a.upd_SUN_Import_date,a.Received,a.Received_Date, a.Receptionist,a.upd_remark "
                        + "from Daily_Trans a LEFT JOIN ab_account_code b "
                        + "ON a.upd_pay_acctcode = b.kind_pment where";
            if (pVATCenter != "*ALL")
                sql += " a.upd_vat_center = '" + pVATCenter + "' and ";
            sql += " (substring(VAT_number,6,len(VAT_number))!='00000' or total_amt_vnd <> 0)";
            if (date_type == "date_visit")
                sql += " and (date_visit between '" + fromdate + "' and '" + todate + "')";
            else if (date_type == "UPD_Payment_Date")
                sql += " and (UPD_Payment_Date between '" + fromdate + "' and '" + todate + "')";
            else if (date_type == "Received_Date")
                sql += " and (Received_Date between '" + fromdate + "' and '" + todate + "')";

            sql += " AND (received is null or received = 0 )";
            //sql += " AND received = 1";
            if (pmt_mode != "*ALL")
                sql += " AND b.kind_pment_desc='" + pmt_mode + "' ";

            sql += " Order by date_visit,CMS_number";
            DataTable tbl = dao.executeQuery(sql);
            /*if (dts.Tables.Count > 0)
                dts.Tables.RemoveAt(0);
            dts.Tables.Add(tbl);*/
            return tbl;
        }

        public DataTable get_Ab_Account_Code(string kind_pment)
        {
            string sql = "SELECT * FROM AB_ACCOUNT_CODE";
            sql += " WHERE KIND_PMENT='" + kind_pment + "'";
            return dao.executeQuery(sql);
        }

        public DataTable get_Invoices_Submitted(string date_type, string pmt_mode, string pVATCenter, string fromdate, string todate)
        { //pmnt_mode_revised,
            // modified INNER JOIN become to LEFT JOIN
            //added upd_remark column on 9/7/2009
            string sql = "select a.Date_visit,a.CMS_number,a.VAT_number,a.VAT_number_revised,a.upd_pay_acctcode, a.pmnt_mode_revised"
                        + ",a.upd_payment_date,a.patient_name,a.total_proc,a.total_phar,a.total_amt,a.total_amt_vnd,a.rate_curr, "
                        + "a.upd_SUN_Import_date,a.Received,a.Received_Date,a.Receptionist,a.upd_remark "
                        + "from Daily_Trans a LEFT JOIN ab_account_code b "
                        + "ON a.upd_pay_acctcode = b.kind_pment where";
            if (pVATCenter != "*ALL")
                sql += " a.upd_vat_center = '" + pVATCenter + "' AND ";

            sql += " (Received_Date between '" + fromdate + "' and '" + todate + "')";
            sql += " AND received = 1";
            if (pmt_mode != "*ALL")
                sql += " AND b.kind_pment_desc='" + pmt_mode + "' ";

            sql += " Order by received_date,CMS_number";
            DataTable tbl = dao.executeQuery(sql);
            /*  if (dts.Tables.Count > 0)
                  dts.Tables.RemoveAt(0);
              dts.Tables.Add(tbl);*/
            return tbl;
        }
        /*
         * This function returns Daily transaction data shows in grid for query
         */
        public DataTable get_Invoices_DailyTrans(string date_type, string pmt_mode, string pVATCenter, string fromdate, string todate)
        { //pmnt_mode_revised,
            // modified INNER JOIN become to LEFT JOIN
            //added upd_remark column on 9/7/2009
            string sql = "select CMS_number as [Invoice #], "+
                            "Date_Visit as [Invoice Date], VAT_number, " +
                            "Patient_Name, TOTAL_PROC as [Procedure], TOTAL_PHAR as [Pharmacy], "+
                            "VAT_Perc as [VAT %], VAT_AMOUNT as [VAT], Total_Amt_USD as [Total_Amt], Total_Amt_VND, " +
                            "Rate_Curr as [Rate], UPD_PAY_ACCTCODE as [Paymt Mode], KIND_PMENT_DESC as [Paymt Type], JOB_CORP, COMP_TEL3 " +
                        "from VR_Transaction_List a LEFT OUTER JOIN ab_account_code b "
                        + "ON a.upd_pay_acctcode = b.kind_pment where";
            if (pVATCenter != "*ALL")
                sql += " a.upd_vat_center = '" + pVATCenter + "' AND ";

            sql += " (" + date_type + " between '" + fromdate + "' and '" + todate + "')";
            //sql += " AND received = 1";
            if ((pmt_mode != "*ALL") && (pmt_mode != "NOT VOID"))
                sql += " AND b.kind_pment_desc='" + pmt_mode + "' ";
            if (pmt_mode == "NOT VOID")
                sql += " AND upd_pay_acctcode<>'VOID'";

            sql += " Order by " + date_type + ",CMS_number";
            //MessageBox.Show(sql);
            DataTable tbl = dao.executeQuery(sql);
            /*  if (dts.Tables.Count > 0)
                  dts.Tables.RemoveAt(0);
              dts.Tables.Add(tbl);*/
            //MessageBox.Show(tbl.Rows.Count.ToString());
            return tbl;
        }

        public int update_DB(string date_visit, string cms_nber, string vat_nber_revised,
                            int received, string upmode_revised, string received_date,
                            string rcpt_name, string upd_remark)
        {
            return dao.update_DailyTrans(date_visit, cms_nber, vat_nber_revised, received, upmode_revised, received_date, rcpt_name, upd_remark);
        }

        public int check_VAT_number(string VAT_number)
        {
            string sql = "SELECT VAT_number,CMS_number FROM Daily_Trans "
                          + " WHERE VAT_number='" + VAT_number + "'";
            DataTable tbl = dao.executeQuery(sql);
            int rnum = tbl.Rows.Count;
            return rnum;
        }

        internal DataTable getUser(string userID, string encodedPass)
        {
            string sql = "SELECT * from tblUser where userID='" + userID + "'"
                        + " And userPassword='" + encodedPass + "'";
            return dao.executeQuery(sql);
     
        }
        /*public void runDTS(string sqlcode)
        {
            dao.runDTS(sqlcode);
        }*/

        internal void usp_InsertIntoDailyTransTemp(string dfrom, string dto)
        {
            dao.usp_InsertIntoDailyTransTemp(dfrom, dto);
        }

        internal void usp_InsertIntoDailyTrans(string dfrom, string dto)
        {
            dao.usp_InsertIntoDailyTrans(dfrom, dto);
        }

        internal int usp_Insert_AB_ACCOUNT_CODE(string kind_pment, string acc_deb,
            string acc_name, string acc_cre_med, string acc_cre_serv, string acc_cre_drug,
            string acc_cre_disp, string t0_code, string t4_code, string kind_pment_desc)
        {
            return dao.usp_Insert_AB_ACCOUNT_CODE(kind_pment, acc_deb, acc_name, acc_cre_med, acc_cre_serv,
                acc_cre_drug, acc_cre_disp, t0_code, t4_code, kind_pment_desc);
        }

        internal int usp_Update_AB_ACCOUNT_CODE(string kind_pment, string acc_deb,
            string acc_name, string acc_cre_med, string acc_cre_serv, string acc_cre_drug,
            string acc_cre_disp, string t0_code, string t4_code, string kind_pment_desc)
        {
            return dao.usp_Update_AB_ACCOUNT_CODE(kind_pment, acc_deb, acc_name, acc_cre_med, acc_cre_serv,
                acc_cre_drug, acc_cre_disp, t0_code, t4_code, kind_pment_desc);
        }

        internal int usp_Delete_AB_ACCOUNT_CODE(string kind_pment)
        {
            return dao.usp_Delete_AB_ACCOUNT_CODE(kind_pment);
        }

        internal bool Check_UserID(string userID)
        {
            bool isvalid = false;
            string sql = "SELECT * from tblUser WHERE userID='" + userID + "'"                       
                        + " AND valid_flag = 1";
            DataTable tbl = dao.executeQuery(sql);
            if (tbl.Rows.Count > 0)
                isvalid = true;
            return isvalid;
        }

        internal bool isNumeric(string p)
        {
            try
            {
                int t = int.Parse(p);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        internal int InsertIntoDailyTransReport(string pStartnum, string pEndnum, string pVATcenter)
        {
            return dao.InsertIntoDailyTransReport(pStartnum, pEndnum, pVATcenter);
        }

        internal DataTable get_Invoice(string vatCenter, string vatNum)
        {
            
            return dao.get_Invoice(vatCenter, vatNum);
        }

        internal DataTable get_Reason_Type()
        {
            return dao.get_Reason_Type();
        }

        internal DataTable get_Description(string pReasonID)
        {
            return dao.get_Description(pReasonID);
        }

        internal void update_Invoice_VOID(string vatcenter,string vatnum, string upd_acctcode, 
                                            string upd_inv_stat, string upd_pmnt_date, string upd_remark,
                                            string upd_reason, string upd_reason_type)
        {
            dao.update_Invoice_VOID(vatcenter, vatnum,
                upd_acctcode, upd_inv_stat, upd_pmnt_date, upd_remark, upd_reason, upd_reason_type);
        }
      
        internal void update_Proc_Phar_VOID(string uCMS_INV_Nber)
        {
            string sql = "Update NEW_PROC_PHAR "
                        + " SET UPD_INV_STAT = '" + "VOID" + "'"
                        + " where INV_NBer='" + uCMS_INV_Nber + "'";
            dao.executeNonquery(sql);
        }

        internal int Update_Received_VAT00000_DailyTrans(string pFromdate, string pTodate)
        {
            return dao.exec_proc("sp_Update_Received_VAT00000_DailyTrans", pFromdate, pTodate);
        }

        internal void check_cms_sequence(string dateFrom, string dateTo)
        {
            string sql = "Select * from new_revenue "
                        + " where date_visit between '" + dateFrom + "' and '" + dateTo + "'";
            //dao.check_cms_sequence(dateFrom, dateTo);
            DataTable tbl = dao.executeQuery(sql);
            if (tbl == null || tbl.Rows.Count == 0)
                return;

            ArrayList arr = new ArrayList();
            int inv_nber = 0;
            foreach (DataRow r in tbl.Rows)
            {
                inv_nber = int.Parse(r["inv_nber"].ToString());
                if (arr.IndexOf(inv_nber) == -1)
                    arr.Add(inv_nber);
            }
            arr.Sort();
            ArrayList arrMiss = check_missing(arr);
            if (arrMiss != null && arrMiss.Count > 0)
            {
                insert_CMS_Checking(arrMiss);
            }
        }

        private void insert_CMS_Checking(ArrayList arrMiss)
        {
            for (int i = 0; i < arrMiss.Count; i++)
            {
                dao.insert_CMS_Checking(arrMiss[i].ToString(),DateTime.Now.ToShortDateString());
            }
        }

        private ArrayList check_missing(ArrayList arr)
        {
            if (arr.Count <= 0)
                return null;
            int startNum = (int)arr[0];
            int endNum = (int)arr[arr.Count - 1];
            ArrayList arrMiss = new ArrayList();
            for(int i = startNum ; i <= endNum ; i++)
            {
                if (arr.IndexOf(i) == -1)
                    arrMiss.Add(i);
            }
            return arrMiss;
        }

        internal void Append_RevVN2Rev(string uDateFrom, string uDateto)
        {
            dao.exec_proc("sp_Append_RevVN2Rev", uDateFrom, uDateto);            
        }

        internal void Append_Proc_PharVN2Proc_Phar(string uDateFrom, string uDateto)
        {
            dao.exec_proc("sp_Append_Proc_PharVN2Proc_Phar", uDateFrom, uDateto);
        }

        internal void Update_pmnt_mode_NEW_REVENUE(string pDatevisit, string pInvnumber, string pVAT_revised, string pPmnt_revised, string pUpd_pmnt_date)
        {
            //int num = 0;
            string sql1 = "Update NEW_REVENUE set ";
            string sql2 = "Update NEW_REVENUE_VN set ";
            if (pVAT_revised.Equals("") == false)
            {
                sql1 += " UPD_VAT_NUM='" + pVAT_revised + "'";
                sql2 += " UPD_VAT_NUM='" + pVAT_revised + "'";
                if (pPmnt_revised.Equals("") == false)
                {
                    sql1 += ",";
                    sql2 += ",";
                }
            }
            if (pPmnt_revised.Equals("") == false)
            {
                sql1 += " UPD_PAY_ACCTCODE='" + pPmnt_revised + "'";
                sql2 += " UPD_PAY_ACCTCODE='" + pPmnt_revised + "'";
            }
            if (pPmnt_revised.Equals("VOID") == true)
            {
                sql1 += ", INV_STAT='V1', UPD_INV_STAT='VOID'";
                sql2 += ", INV_STAT='V1', UPD_INV_STAT='VOID'";
            }
            sql1 += ", UPD_PAYMENT_DATE='" + pUpd_pmnt_date + "'";
            sql2 += ", UPD_PAYMENT_DATE='" + pUpd_pmnt_date + "'";

            sql1 += " where date_visit='" + pDatevisit + "' and INV_NBER='" + pInvnumber + "'";
            sql2 += " where date_visit='" + pDatevisit + "' and INV_NBER='" + pInvnumber + "'";
            try
            {
                dao.executeNonquery(sql1);
                dao.executeNonquery(sql2);
            }
            catch (Exception ex)
            {                
            }
        }       
    }

}
